import tushare as ts
import pymysql
from sqlalchemy import create_engine
from urllib.parse import quote_plus
from datetime import datetime

# 配置 MySQL 连接信息
# %40 表示 @ 符号，%23 表示 # 符号
password = quote_plus('NewPass123!@#')
engine = create_engine(f'mysql+pymysql://root:{password}@10.54.1.6:13306/zjjk')

# 设置 tushare 的 token
pro = ts.pro_api('b1125846b73e4e0445d2d8cd1ea3d7ba1443e881c78eb09acb78f6ec')

# 从 tushare_stock_basic 表中获取所有 ts_code
import pandas as pd
stock_basic_data = pd.read_sql('SELECT ts_code FROM tushare_stock_basic', engine)

# 遍历每个 ts_code
def fetch_daily_data_by_date_all():
    for index, row in stock_basic_data.iterrows():
        ts_code = row['ts_code']
        # 将数据写入以 ts_code 命名的表中
        t = ts_code.replace('.', '_').lower()
        table_name = f'tushare_{t}'
        try:
            # 调用 daily 接口获取历史行情数据
            data = pro.daily(ts_code=ts_code)
            if not data.empty:

                data.to_sql(table_name, engine, if_exists='replace', index=False)
                print(f'{ts_code} 的历史行情数据已成功写入 {table_name} 表')
            else:
                print(f'{ts_code} 未获取到历史行情数据')
        except Exception as e:
            print(f'{table_name} 数据获取或写入失败: {str(e)}')

def fetch_daily_data_by_date(begin_date,end_date):
    """增量获取指定日期的股票 daily 数据并插入对应表中。

    :param target_date: 指定日期，格式为 'YYYYMMDD'
    """
    for index, row in stock_basic_data.iterrows():
        ts_code = row['ts_code']
        try:
            # 调用 daily 接口获取指定日期的历史行情数据
            data = pro.daily(ts_code=ts_code, begin_date=begin_date, end_date=end_date)
            if not data.empty:
                # 将数据写入以 ts_code 命名的表中
                t = ts_code.replace('.', '_')
                table_name = f'tushare_{t}'
                data.to_sql(table_name, engine, if_exists='append', index=False)
                print(f'{ts_code} 在 {begin_date} 的历史行情数据已成功插入 {table_name} 表')
            else:
                print(f'{ts_code} 在 {begin_date} 未获取到历史行情数据')
        except Exception as e:
            print(f'{ts_code} 在 {begin_date} 数据获取或写入失败: {str(e)}')        

def fetch_stocks_without_doubling(start_date='20240924'):
    """列举 trade_date 从指定日期以后的股票，计算历史最高位和最低位的倍数，打印未翻倍的股票。

    :param start_date: 起始日期，格式为 'YYYYMMDD'，默认为 '20240924'
    """
    results = []
    for index, row in stock_basic_data.iterrows():
        ts_code = row['ts_code']
        t = ts_code.replace('.', '_')
        table_name = f'tushare_{t}'
        try:
            # 从数据库中获取指定日期之后的数据
            query = f"SELECT high, low FROM {table_name} WHERE trade_date >= '{start_date}'" 
            data = pd.read_sql(query, engine)
            if not data.empty:
                # 获取历史最高位和最低位
                max_high = data['high'].max()
                min_low = data['low'].min()
                ratio = max_high / min_low
                results.append({
                    'ts_code': ts_code,
                    'max_high': max_high,
                    'min_low': min_low,
                    'ratio': ratio
                })
        except Exception as e:
            print(f'{ts_code} 数据查询失败: {str(e)}')

    # 将结果转换为 DataFrame 并按倍数排序
    result_df = pd.DataFrame(results)
    result_df.sort_values(by='ratio', inplace=True)
    
    # 打印未翻倍的股票
    non_doubling_stocks = result_df[result_df['ratio'] < 2]
    print('20240924 至今未翻倍的股票:')
    print(non_doubling_stocks[['ts_code', 'max_high', 'min_low', 'ratio']])
    return non_doubling_stocks

if __name__ == '__main__':
    end_date = datetime.now().strftime('%Y%m%d')
    #fetch_daily_data_by_date('20250807',end_date) 
    #fetch_daily_data_by_date_all()
    non_doubling_stocks = fetch_stocks_without_doubling()
    non_doubling_stocks.to_csv('non_doubling_stocks.csv')